select 
d.phid_pc--项目id
,m.phid_org
,m.creator--负责人
,m.u_ent_date--到期时间
,m.bill_dt 
,d.u_ysje
,d.u_hklx--回款类型
,d.u_bzcs--跟踪节点
from p_form_track_insert_d d
left join p_form_track_insert_m m on m.phid =d.pphid



CREATE OR REPLACE PROCEDURE create_return_task
IS
    -- 定义游标，从 p_form_track_insert_d 和 p_form_track_insert_m 表中查询数据
    CURSOR cur_data IS
        SELECT 
            d.phid_pc,
            m.phid_org,
            m.creator,
            m.u_ent_date,
            m.bill_dt, 
            d.u_ysje,
            d.u_hklx,
            d.u_bzcs
        FROM 
            p_form_track_insert_d d
        LEFT JOIN 
            p_form_track_insert_m m ON m.phid = d.pphid;
    
    -- 定义变量存储新生成的 phid
    v_new_phid_m VARCHAR2(50);
    v_new_phid_d NUMBER;
    -- 定义要插入的记录数量
    v_insert_count NUMBER := 5; 
BEGIN
    -- 遍历游标中的每一行数据
    FOR rec IN cur_data LOOP
        -- 判断到期时间是否为明天
        IF rec.u_ent_date = TRUNC(SYSDATE + 1) THEN
            -- 生成 p_form_return_task_m 表的新 phid（使用时间戳）
            v_new_phid_m := TO_CHAR(SYSTIMESTAMP, 'YYYYMMDDHH24MISSFF');
            
            -- 插入数据到 p_form_return_task_m 表
            INSERT INTO p_form_return_task_m (phid, u_creator, phid_pc, u_gzjd, phid_org, creator,u_jhhk_date,u_jjhk_amt,bill_dt,phid_fill_psn)
            VALUES (v_new_phid_m, rec.creator, rec.phid_pc, rec.u_bzcs, rec.phid_org, rec.creator,rec.u_ent_date,rec.u_ysje,rec.bill_dt,rec.creator);
            
            -- 生成 p_form_return_task_d 表的初始 phid（使用时间戳转换为数字）
            v_new_phid_d := TO_NUMBER(TO_CHAR(SYSTIMESTAMP, 'YYYYMMDDHH24MISSFF'));
            
            -- 循环插入多条记录到 p_form_return_task_d 表
            FOR i IN 1..v_insert_count LOOP
                INSERT INTO p_form_return_task_d (phid, pphid, u_hkjd_name)
                VALUES (v_new_phid_d + i - 1, v_new_phid_m, '0' || i);
            END LOOP;
        END IF;
    END LOOP;
    
    -- 提交事务
    COMMIT;
EXCEPTION
    -- 异常处理：若插入过程中出现错误，回滚事务
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

-- 创建序列
CREATE OR REPLACE PROCEDURE create_return_task
IS
    -- 定义游标，从 p_form_track_insert_d 和 p_form_track_insert_m 表中查询数据
    CURSOR cur_data IS
        SELECT 
            d.phid_pc,
            m.phid_org,
            m.creator,
            m.u_ent_date,
            m.bill_dt, 
            d.u_ysje,
            d.u_hklx,
            d.u_bzcs,
            d.phid  -- 新增获取 p_form_track_insert_d 表的 phid
        FROM 
            p_form_track_insert_d d
        LEFT JOIN 
            p_form_track_insert_m m ON m.phid = d.pphid;
    
    -- 定义变量存储新生成的 phid
    v_new_phid_m NUMBER;
    v_new_phid_d NUMBER;
    -- 定义要插入的记录数量
    v_insert_count NUMBER := 5; 
    -- 定义变量存储 p_form_return_task_m 表中最大的 phid
    v_max_phid_m NUMBER;
    -- 定义变量存储 p_form_return_task_d 表中最大的 phid
    v_max_phid_d NUMBER;
    -- 定义变量用于存储查询到的记录数量，修改为 NUMBER 类型
    v_skip_insert NUMBER;
BEGIN
    -- 遍历游标中的每一行数据
    FOR rec IN cur_data LOOP
        -- 判断到期时间是否为明天
        IF rec.u_ent_date = TRUNC(SYSDATE + 1) THEN
            -- 检查 p_form_return_task_m 表中的 u_source_phid 是否等于本次循环 p_form_track_insert_d 表的 phid
            SELECT COUNT(*) INTO v_skip_insert
            FROM p_form_return_task_m
            WHERE u_source_phid = rec.phid;
            
            IF v_skip_insert > 0 THEN
                -- 如果存在相等的记录，跳过本次插入
                CONTINUE;
            END IF;
            
            -- 查询 p_form_return_task_m 表中最大的 phid
            SELECT NVL(MAX(TO_NUMBER(phid)), 8570000000000001) INTO v_max_phid_m
            FROM p_form_return_task_m;
            
            -- 新的 phid 为最大 phid 加 1
            v_new_phid_m := v_max_phid_m + 1;
            
            -- 插入数据到 p_form_return_task_m 表
            INSERT INTO p_form_return_task_m (phid, u_creator, phid_pc, u_gzjd, phid_org, creator,u_jhhk_date,u_jjhk_amt,bill_dt,phid_fill_psn,u_source_phid,ng_record_ver,cur_orgid,ng_phid_org,ng_phid_cu,ng_phid_original,ng_phid_bp,ng_orgid_original,ng_phid_ui_scheme,product_category,ng_write_source,ng_share_sign,ng_sv_search_key,ng_sd_search_key,ng_data_status,arc_flag,asr_flag,app_status,wf_flag)
            VALUES (v_new_phid_m, rec.creator, rec.phid_pc, rec.u_bzcs, rec.phid_org, rec.creator,rec.u_ent_date,rec.u_ysje,rec.bill_dt,rec.creator,rec.phid,1,1,1,1,0,1,0,0,'i8',0,5,10,1,0,0,0,0,0);
            
            -- 查询 p_form_return_task_d 表中最大的 phid
            SELECT NVL(MAX(TO_NUMBER(phid)), 8570000000000001) INTO v_max_phid_d
            FROM p_form_return_task_d;
            
            -- p_form_return_task_d 表的初始 phid 为最大 phid 加 1
            v_new_phid_d := v_max_phid_d + 1;
            
            -- 循环插入多条记录到 p_form_return_task_d 表
            FOR i IN 1..v_insert_count LOOP
                INSERT INTO p_form_return_task_d (phid, pphid, u_hkjd_name)
                VALUES (v_new_phid_d + i - 1, v_new_phid_m, '0' || i);
            END LOOP;
        END IF;
    END LOOP;
    
    -- 提交事务
    COMMIT;
EXCEPTION
    -- 异常处理：若插入过程中出现错误，回滚事务
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

--创建定时任务
BEGIN
    -- 创建作业
    DBMS_SCHEDULER.CREATE_JOB (
        job_name        => 'execute_create_return_task_job',
        job_type        => 'STORED_PROCEDURE',
        job_action      => 'create_return_task',
        start_date      => TRUNC(SYSDATE + 1), -- 从明天 0:00 开始
        repeat_interval => 'FREQ=DAILY;BYHOUR=0;BYMINUTE=0;BYSECOND=0',
        enabled         => TRUE,
        comments        => 'Execute create_return_task at 0:00 every day'
    );
END;
/